<?php
/**
 * 更新region表的区的房源数和均价
 */

namespace app\crontab\controller\always;


use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;

class AreaHouseNum extends Command {

    protected function configure() {
        $this->setName('AreaHouseNum');
    }

    protected function execute(Input $input, Output $output) {
        $this->initial();
    }

    public function initial() {
        echo "start\n";
        set_time_limit(0);
        $region = Db::name('region')
            ->where('rank', 3)
//            ->where('p_code', 510100)
            ->field('city_code,id,name')
            ->select();
        //统计平均价
        $re1 = self::avg_price($region);
        //统计房源数
        $re2 = self::house_number($region);
        echo 'total:' . count($region) . "\n";
        echo 'avg_price成功->' . $re1 . "\n";
        echo 'house_number成功->' . $re2 . "\n";
        echo "end\n";
    }

    /**
     * 统计 avg_price
     * @param $region
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public static function avg_price($region) {
        $int = 0;
        foreach ($region as $k => $v) {
            //有面积的、未结束的、不管是否上下架
            $data = Db::name('house')
                ->alias('h')
                ->join('house_auction ha', 'ha.id=h.auction_id')
                ->where('h.build_area', '>', 0)
                ->where('h.city_code', $v['city_code'])
                ->where('ha.auction_status', 'in', [1, 2])
                ->field('ha.initial_price,h.build_area')
                ->select();
            $count = count($data);
            $avg = 0;
            if ($count > 0) {
                $total_price = 0;
                $total_area = 0;
                foreach ($data as $item) {
                    $total_price += $item['initial_price'];
                    $total_area += $item['build_area'];
                }
                $total_price = moneyFormat($total_price, 2);
                $total_area = $total_area / 10000;
                $avg = moneyFormat(intval($total_price / $total_area), 1);
            }
            if (Db::name('region')
                    ->where('id', $v['id'])
                    ->update(['avg_price' => $avg]) !== false) $int++;
        }
        return $int;
    }

    /**
     * 统计房源数
     * @param $region
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public static function house_number($region) {
        $int = 0;
        foreach ($region as $k => $v) {
            //待开始&进行中
            $house_number = Db::name('house')
                ->alias('h')
                ->join('house_auction ha', 'ha.id=h.auction_id')
                ->where('h.city_code', $v['city_code'])
                ->where('h.is_shelf', 1)
                ->where('h.deleted', 0)
                ->where('ha.auction_status', 'in', [1, 2])
                ->count();
            //总数
            $total_number = Db::name('house')
                ->where('city_code', $v['city_code'])
                ->where('is_shelf', 1)
                ->where('deleted', 0)
                ->count();
            $data = compact('house_number', 'total_number');
            if (Db::name('region')->where('id', $v['id'])->update($data) !== false) $int++;
        }
        return $int;
    }

}